"""
类方法：
get_conn(cls): 建立连接，获取连接对象
query_one(cls, sql): 查询一条数据
query_all(cls, sql): 查询全部数据
iud_data(cls, sql): 执行数据库增删改
"""

# 1.导入pymysql
import pymysql


class DBUtil(object):
    @classmethod
    def get_conn(cls):
        conn = pymysql.connect(host='211.103.136.244', port=7061,
                               user='student', password='iHRM_student_2021',
                               database='ihrm', charset='utf8')
        return conn

    @classmethod
    def query_one(cls, sql):
        conn = cls.get_conn()
        cursor = conn.cursor()
        cursor.execute(sql)
        ret = cursor.fetchone()
        cursor.close()
        conn.close()
        return ret

    @classmethod
    def query_many(cls, sql, num):
        conn = cls.get_conn()
        cursor = conn.cursor()
        cursor.execute(sql)
        ret = cursor.fetchmany(num)
        cursor.close()
        conn.close()
        return ret

    @classmethod
    def query_all(cls, sql):
        conn = cls.get_conn()
        cursor = conn.cursor()
        cursor.execute(sql)
        ret = cursor.fetchall()
        cursor.close()
        conn.close()
        return ret

    @classmethod
    def iud_data(cls, sql):
        conn = cls.get_conn()
        cursor = conn.cursor()
        i = 0
        try:
            i = cursor.execute(sql)
        except Exception as e:
            print(f'e={e}')
            conn.rollback()  # 异常回滚
        else:
            conn.commit()  # 没有异常，提交事务
        finally:
            cursor.close()  # 关闭游标
            conn.close()  # 关闭连接
        return i


if __name__ == '__main__':
    mobile = '15821747884'  # 手机号

    # 查询一个结果
    sql = f"select * from bs_user where mobile='{mobile}'"
    one = DBUtil.query_one(sql)
    print(one)

    # 删除
    sql = f"delete from bs_user where mobile='{mobile}'"
    n = DBUtil.iud_data(sql)
    print(n)

    # 查询一个结果
    sql = f"select * from bs_user where mobile='{mobile}'"
    one = DBUtil.query_one(sql)
    print(one)
